﻿using aier.dataaccess.model;
using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;

namespace aier.dataaccess.dapper
{
    public class ContactDal
    {
        public DapperHelper dapperHelper { get; set; }

        /// <summary>
        /// 根据联系人id查询联系人
        /// </summary>
        /// <param name="contactId"></param>
        /// <returns></returns>
        public ContactView QryContact(Guid contactId) {

            return dapperHelper.ExecueWithoutTransaction<ContactView>(a =>
            {
                string sql = @"select * from AIER_CONTACT_INFO /**where**/";
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                sqlBuilder.Where("contactid = @contactId", new { contactId = contactId });
                //sqlBuilder.AddParameters(new { siteid= hospitalId });
                return a.Query<ContactView>(sqlTemplate.RawSql, sqlTemplate.Parameters).First();
            });
        }

        /// <summary>
        /// 根据区域、负责人、合作意向、活跃度、开始时间、结束时间查询联系人(时间格式：YYYY-MM-DD)
        /// </summary>
        /// <param name="new_districtandcounty">区域</param>
        /// <param name="new_liveness">活跃度</param>
        /// <param name="new_cooperativeintention">合作意向</param>
        /// <param name="startDate">开始时间(YYYY-MM-DD)</param>
        /// <param name="endDate">结束时间(YYYY-MM-DD)</param>
        /// <param name="ownerid">负责人</param>
        /// <returns></returns>
        public IEnumerable<ContactView> QryContactList(Guid new_districtandcounty, int? new_liveness
                    , bool? new_cooperativeintention, string startDate, string endDate, Guid ownerid) {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<ContactView>>(a =>
            {
                string sql = @"select * from AIER_CONTACT_INFO /**where**/";
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                sqlBuilder.Where("new_type = 1");
                if (new_districtandcounty != Guid.Empty)
                {
                    sqlBuilder.Where("new_districtandcounty = @new_districtandcounty",new { new_districtandcounty= new_districtandcounty } );
                }
                if (new_liveness != null)
                {
                    sqlBuilder.Where("new_liveness = @new_liveness", new { new_liveness = new_liveness });
                }
                if (new_cooperativeintention != null)
                {
                    sqlBuilder.Where("new_cooperativeintention = @new_cooperativeintention", new { new_cooperativeintention = new_cooperativeintention });
                }
                if (ownerid != Guid.Empty&&ownerid!=null)
                {
                    sqlBuilder.Where("ownerid = @ownerid", new { ownerid = ownerid });
                }
                if (!string.IsNullOrWhiteSpace(startDate))
                {
                    sqlBuilder.Where("Createdon >= @startDate", new { startDate = DateTime.Parse(startDate) });
                }
                if (!string.IsNullOrWhiteSpace(endDate))
                {

                    sqlBuilder.Where("Createdon < @endDate", new { endDate = DateTime.Parse(endDate)});
                }
                sqlBuilder.OrderBy("new_liveness asc");
                return a.Query<ContactView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });
        }

        /// <summary>
        /// 根据区域和负责人查询联系人
        /// </summary>
        /// <param name="new_districtandcounty">区域</param>
        /// <param name="new_liveness">活跃度</param>
        /// <param name="ownerid">负责人</param>
        /// <returns></returns>
        //public IEnumerable<ContactView> QryContactList(Guid new_districtandcounty, int? new_liveness, Guid ownerid) {
        //    return QryContactList(new_districtandcounty, new_liveness,null,null,null,ownerid);
        //}

        /// <summary>
        /// 根据姓名、负责人查询联系人
        /// </summary>
        /// <param name="name">姓名</param>
        /// <param name="ownerid">负责人</param>
        /// <returns></returns>
        public IEnumerable<ContactView> QryContactListByNameAndOwnerId(string name, Guid ownerid) {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<ContactView>>(a =>
            {
                string sql = @"select * from AIER_CONTACT_INFO /**where**/";
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                if (!string.IsNullOrWhiteSpace(name))
                {
                    sqlBuilder.Where($"LastName like '%{name}%'");
                }
                if (ownerid != Guid.Empty&&ownerid!=null)
                {
                    sqlBuilder.Where("ownerid = @ownerid", new { ownerid = ownerid });
                }
                sqlBuilder.OrderBy("new_liveness asc");
                return a.Query<ContactView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });
        }

        /// <summary>
        /// 根据负责人查询区域下拉列表
        /// </summary>
        /// <param name="ownerid">负责人</param>
        /// <param name="type"></param>
        /// <returns></returns>
        public IEnumerable<ContactView> QryDistrictandcountyList(Guid ownerid,int? type) {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<ContactView>>(a =>
            {
                string sql = @" select distinct new_districtandcounty,new_marketname+'-'+new_districtandcountyName as new_districtandcountyName
                                          from dbo.AIER_CONTACT_INFO /**where**/";
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                sqlBuilder.Where("new_districtandcounty is not null");
                if (type!=null)
                {
                    sqlBuilder.Where("new_type = @type",new { type=type});
                }
                if (ownerid != Guid.Empty&&ownerid!=null)
                {
                    sqlBuilder.Where("ownerid = @ownerid", new { ownerid = ownerid });
                }
              
                return a.Query<ContactView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });
        }
    }
}
